;;;;we're trying to parse text returned from queries
;;;I've decided to christen this thing: sql-ms-query-parse
(defun sql-ms-query-parse-find-dashes ()
  "Used to find the first dashed line in a buffer from point UPWARDS.
  The dashed line separates the column headings and values
  in the string output of a query"
					;(interactive)
  (re-search-backward "^ [-]+[ ]*"))


(defun sql-ms-query-parse-get-column-start-end()
  "For a given position, tries to find the starting and
   ending columns(buffer positions) of a given column (sql results),
   using the dashed separator in sql text output."
;  (interactive)
  (search-forward "-")
  (backward-char 1)
  (setf colstart (current-column))
  (search-forward " ")
  (if (eql (current-column) 1) (backward-char 2) (backward-char 1))
  (setf colend (- (current-column) 0))
  (list colstart colend))


(defun sql-ms-query-parse-get-columns-and-line()
  "Used to get the line position of the dashed line,
   and the start and end columns (buffer) of the columns (sql query)"
  ;(end-of-buffer);bit doubtful about whether we should do this
  (re-search-backward ".+")
  (setf end-line (line-number-at-pos))
  (sql-ms-query-parse-find-dashes)
  (end-of-line)
  (setf line-end (- (current-column) 1)) 
  (beginning-of-line)
  (setf columns '())
  (while (< (current-column) line-end)
    (progn
      (push (sql-ms-query-parse-get-column-start-end) columns)))
  (list :columns (reverse columns) :line (+ (line-number-at-pos) 1) :end-line end-line))

(setf *res* nil)


(defun sql-ms-query-parse-get-vals (line cols)
  (goto-line line)
  (setf values '())
  (setf colcount 0)
  (while (> (length cols) 0)
    (progn
      (beginning-of-line)
      (setf se (pop cols))
      (forward-char (first se))
      (setf mystart (point))
      (beginning-of-line)
      (forward-char (second se))
      (setf myend (point))
      (setf whole-match (buffer-substring-no-properties mystart myend))
      (setf whole-match (sql-ms-query-parse-chomp whole-match))
      (message (concat "->" whole-match))
					;(push colcount values)
      (push whole-match values)
      (incf colcount)))
  (reverse values))



(defun sql-ms-query-parse-chomp (str)
  "This function has been stolen off the web
I think the right person should get credit for it
but I can't for the LIFE of me remember where I found it..."
  (let ((s (if (symbolp str)(symbol-name str) str)))
    (save-excursion
      (while (and
	      (not (null (string-match "^\\( \\|\f\\|\t\\|\n\\)" s)))
	      (> (length s) (string-match "^\\( \\|\f\\|\t\\|\n\\)" s)))
	(setq s (replace-match "" t nil s)))
      (while (and
	      (not (null (string-match "\\( \\|\f\\|\t\\|\n\\)$" s)))
	      (> (length s) (string-match "\\( \\|\f\\|\t\\|\n\\)$" s)))
	(setq s (replace-match "" t nil s))))
    s))

(defun sql-ms-query-parse-listtohash (list)
  "converts the list containing integers and string values to
a hash table, so you can refer to columns(SQL QUERY) by name
instead of index"
  (setf h (make-hash-table :test 'equal))
  (while (> (length list) 0)
    (puthash (pop list) (pop list) h))
  h)

  (defun smapmm-run-query (qstr)
    (sql-send-string qstr)
    (sql-send-string "go"))


  (defun smapmm-clear-other-window (other-window this-window)
    (select-window other-window)
    (delete-region (point-min) (point-max))
    (select-window this-window))


;get everything, so we can start doing all sorts of funky... queries
(defun sql-ms-query-results-of-query-get()
"Wrapper around a couple of functions. Call this in a *SQL* type buffer, and you end up getting a list back with the column headings and values of each tuple."
  (setf columns-and-line (sql-ms-query-parse-get-columns-and-line))
  (setf headings (sql-ms-query-parse-get-vals (- (getf columns-and-line :line) 2) (getf columns-and-line :columns)))
  (setf resultset '())
  (setf inc (- (getf columns-and-line :end-line) (- (getf columns-and-line :line) 1)))
  (while (>= (decf inc) 0)
    (message (concat "INC->:" (number-to-string inc)))
    (push (sql-ms-query-parse-get-vals (+ (getf columns-and-line :line) inc) (getf columns-and-line :columns)) resultset)
        ;(push inc resultset)
    )
  ;(setf first-line (get-vals (+ (getf columns-and-line :line) 1) (getf columns-and-line :columns)))
  (list :headings headings :tuples resultset))


(defun sql-ms-munge()
  "Just a wrapper around sql-ms-query-results-of-query-get so you don't have to call that great big long name"
;  (interactive)
  (setf *res* (sql-ms-query-results-of-query-get)))

(defun tabify-list (lin)
  (mapcar #'(lambda (x) (insert (concat x "\t")) )lin)
  (newline))

(defun spit-table (table)
  (mapcar #'(lambda (x) (tabify-list x)) table))

(defun convertTextToTabs()
;  (interactive)
  (progn
    (sql-ms-munge)
    (end-of-buffer)
    (insert "*** CONVERTED TAB SEPARATED ITEMS BELOW ***\n")
    (tabify-list (getf *res* :headings))
    (spit-table (getf *res* :tuples))))

;;;we treat the format we receive from sql-ms as the default. Other supplied formats must be converted.
(setf columns '())

(setf conversion-data '())
(defun rpt2sql-ms ()
 ; (interactive)
  (setf end-point (line-number-at-pos));the last line of the change.
  (re-search-backward "^[-]+[ ]*")
  (setf start-point (line-number-at-pos))
  (previous-line)
					;(end-of-line)
					;(insert " ")
  (beginning-of-line)
  (insert " ")
  (next-line)
  (end-of-line)
  (insert " ")
  (beginning-of-line)
  (insert " ")

  (setf columns '())

  (end-of-line)
  (setf line-end (- (current-column) 1)) 
  (beginning-of-line)


  ;something odd with the position of the end-point
  (while (and (< (current-column) line-end) (< (line-number-at-pos) end-point))
    (progn
      (message (concat "->" (number-to-string (line-number-at-pos)) ":"  (number-to-string (current-column))))
      (push (sql-ms-query-parse-get-column-start-end) columns)))
					;(reverse columns)
  (setf end-column (cdr (pop columns)))
  ;columns
  
  ;okay, so we know what the target column is.
  (goto-line (+ start-point 0))
  (while (> (- end-point (line-number-at-pos)) 0)
    (message (concat "end-point->:" (number-to-string end-point)))
    (next-line)
    (beginning-of-line)
    (insert " ")
    (end-of-line)
    (message (concat "end-column->:" (number-to-string (car end-column))))
    (pad-to-column (car end-column))
    )
  )

(defun pad-to-column (target-column &optional pad-character )
;  (interactive)
  (setf target-column (- target-column (current-column)))
  (if (eq nil pad-character)
      (setf pad-character " "))
  (while (<= 0 (decf target-column))
    (insert pad-character)))

  (defun rpt2tabs ()
    (interactive)
    (rpt2sql-ms)
    (convertTextToTabs))
    
    